﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Common;
using Dto;

namespace SqlServerDAL
{
    public class AdminDAL
    {
        private const string WAMING = "SqlServerDAL.AdminDAL";
        private const string CompleteSql = "select * from Admin";
        private const string CountSql = "select count(AdminID) from Admin";

        public Hashtable GetAdmin(string adminName, string pwd)
        {
            try
            {
                Hashtable ht = null;
                var parameters = new List<SqlParameter>
                                     {
                                         new SqlParameter("@AdminName", SqlDbType.VarChar) {Value = adminName},
                                         new SqlParameter("@AdminPwd", SqlDbType.VarChar) {Value = pwd}
                                     };
                const string sql = CompleteSql + " where AdminName=@AdminName and AdminPwd=@AdminPwd";
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }

                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".GetAdmin(string adminName, string pwd)", ex);
                return null;
            }
        }

        public Hashtable FindById(int id)
        {
            try
            {
                Hashtable ht = null;
                const string sql = CompleteSql + " where AdminID=@AdminID";
                var param = new SqlParameter("@AdminID", SqlDbType.Int) { Value = id };
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, param))
                {
                    if (red.Read())
                    {
                        ht = SqlHelper.FillHashtable(red);
                    }
                }
                return ht;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".FindById(int id)", ex);
                return null;
            }
        }

        #region SlicedFindAll

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult)
        {
            return SlicedFindAll(firstResult, maxResult, "");
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, string adminName)
        {
            return SlicedFindAll(firstResult, maxResult, adminName, 0);
        }

        public List<Hashtable> SlicedFindAll(int firstResult, int maxResult, string adminName, int roleID)
        {
            try
            {
                List<Hashtable> hts;
                var sql = "select row_number() over (order by AdminID) as rowNumber, * from Admin";
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (!string.IsNullOrEmpty(adminName))
                {
                    whereSql += " where AdminName=@AdminName";
                    parameters.Add(new SqlParameter("@AdminName", SqlDbType.VarChar) { Value = adminName });
                }
                if (roleID != 0)
                {
                    whereSql += whereSql == "" ? " where AdminRole=@AdminRole" : " and AdminRole=@AdminRole";
                    parameters.Add(new SqlParameter("@AdminRole", SqlDbType.Int) { Value = roleID });
                }
                sql = string.Format(
                        "select * from (" + sql + whereSql + ") as admin where rowNumber between {0} AND {1} order by AdminID",
                        firstResult, maxResult);

                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    hts = new List<Hashtable>();
                    while (red.Read())
                    {
                        var ht = SqlHelper.FillHashtable(red);
                        hts.Add(ht);
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".SlicedFindAll(int firstResult, int maxResult, string adminName)", ex);
                return null;
            }
        }

        #endregion

        public List<Hashtable> FindAll()
        {
            return FindAll(0);
        }

        public List<Hashtable> FindAll(int roleID)
        {
            try
            {
                List<Hashtable> hts;
                var parameters = new List<SqlParameter>();
                var whereSql = "";
                if (roleID != 0)
                {
                    whereSql += " where AdminRole=@AdminRole";
                    parameters.Add(new SqlParameter("@AdminRole", SqlDbType.Int) { Value = roleID });
                }
                var sql = CompleteSql + whereSql;
                using (var red = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray()))
                {
                    hts = new List<Hashtable>();
                    while (red.Read())
                    {
                        var ht = SqlHelper.FillHashtable(red);
                        hts.Add(ht);
                    }
                }
                return hts;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".FindAll(int roleID)", ex);
                return null;
            }
        }

        #region SelectCount

        public int SelectCount()
        {
            return SelectCount("");
        }

        public int SelectCount(string adminName)
        {
            return SelectCount(adminName, 0);
        }

        public int SelectCount(string adminName, int adminRole)
        {
            try
            {
                var whereSql = "";
                var parameters = new List<SqlParameter>();
                if (!string.IsNullOrEmpty(adminName))
                {
                    whereSql = " where AdminName=@AdminName";
                    parameters.Add(new SqlParameter("@AdminName", SqlDbType.VarChar) { Value = adminName });
                }
                if (adminRole != 0)
                {
                    whereSql = whereSql == "" ? " where AdminRole=@AdminRole" : " and AdminRole=@AdminRole";
                    parameters.Add(new SqlParameter("@AdminRole", SqlDbType.Int) { Value = adminRole });
                }
                var sql = CountSql + whereSql;
                var result = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".SelectCount(string adminName)", ex);
                return -1;
            }
        }

        #endregion

        public bool Save(AdminDto dto)
        {
            try
            {
                string sql;
                var parameters = new List<SqlParameter>();
                if (dto.AdminId == 0)
                {
                    sql =
                        @"insert into Admin(AdminName,AdminPwd,AdminDate,AdminEmail,AdminSex,AdminRole,AdminAddress,
                        AdminTel,AdminTrueName,AdminType) values(@AdminName,@AdminPwd,@AdminDate,@AdminEmail,@AdminSex,
                        @AdminRole,@AdminAddress,@AdminTel,@AdminTrueName,@AdminType)";
                }
                else
                {
                    sql =
                        @"update Admin set AdminName=@AdminName,AdminPwd=@AdminPwd,AdminDate=@AdminDate,AdminEmail=@AdminEmail,
                        AdminSex=@AdminSex,AdminRole=@AdminRole,AdminAddress=@AdminAddress,AdminTel=@AdminTel,
                        AdminTrueName=@AdminTrueName,AdminType=@AdminType where AdminID=@AdminID";
                    parameters.Add(new SqlParameter("@AdminID", SqlDbType.Int) { Value = dto.AdminId });
                }
                parameters.Add(new SqlParameter("@AdminName", SqlDbType.VarChar) { Value = dto.AdminName });
                parameters.Add(new SqlParameter("@AdminPwd", SqlDbType.VarChar) { Value = dto.AdminPwd });
                parameters.Add(new SqlParameter("@AdminDate", SqlDbType.DateTime) { Value = dto.AdminDate });
                parameters.Add(new SqlParameter("@AdminEmail", SqlDbType.VarChar) { Value = dto.AdminEmail });
                parameters.Add(new SqlParameter("@AdminSex", SqlDbType.Int) { Value = dto.AdminSex });
                parameters.Add(new SqlParameter("@AdminRole", SqlDbType.Int) { Value = dto.AdminRoleID });
                parameters.Add(new SqlParameter("@AdminAddress", SqlDbType.VarChar) { Value = dto.AdminAddress });
                parameters.Add(new SqlParameter("@AdminTel", SqlDbType.VarChar) { Value = dto.AdminTel });
                parameters.Add(new SqlParameter("@AdminTrueName", SqlDbType.VarChar) { Value = dto.AdminTrueName });
                parameters.Add(new SqlParameter("@AdminType", SqlDbType.VarChar) { Value = dto.AdminType });

                SqlHelper.ExecuteNonQuery(SqlHelper.connStr, CommandType.Text, sql, parameters.ToArray());
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".Save(AdminDto dto)", ex);
                return false;
            }
        }

        #region Delete

        public bool Delete(int id)
        {
            return Delete(new[] { id });
        }

        public bool Delete(int[] ids)
        {
            try
            {
                const string sql = "delete from Admin where AdminID=@AdminID";
                using (var conn = new SqlConnection(SqlHelper.connStr))
                {
                    conn.Open();
                    var tran = conn.BeginTransaction();
                    try
                    {
                        foreach (var id in ids)
                        {
                            var param = new SqlParameter("@AdminID", SqlDbType.Int) { Value = id };
                            SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql, param);
                        }
                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                    }
                }
                return true;
            }
            catch (Exception ex)
            {
                Log.LogHandler.LogError(WAMING + ".Save(AdminDto dto)", ex);
                return false;
            }
        }

        #endregion
    }
}
